﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Dapper;
using HongStone.Ince;
using HongStone.Domain.ContractModel;
using HongStone.Domain.AttendanceManagement;
using HongStone.Dto;
using HongStone.Dto.ProductionDto;

namespace HongStone.Repository
{
    public class Productiono: ProductionoInce
    {
        private readonly HongStoneContext _app;

        public Productiono(HongStoneContext app)
        {
            _app = app;
        }

        /// <summary>
        /// 显示合同额百分比
        /// </summary>
        /// <returns></returns>
        public List<Productionotemporary> GetThecontracts(string And,string End)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var sql = $"select ((SUM(Actualamount)/100)*10) BaiFen,sum(a.Actualamount) as Zonge,a.Zong BuBiao,b.DictionariesName Name from Thecontract a join DictionariesTable b on a.DictionariesId = b.DictionariesId where Contractsigningdate >= '2021-08-01' and Contractsigningdate<= '2021-12-31' and b.IsDelete='true' group by b.DictionariesName,a.Zong ";

                var list = conn.Query<Productionotemporary>(sql).ToList();

                return list;
            }
        }

        /// <summary>
        /// 部门合同额同比(月)
        /// </summary>
        /// <returns></returns>
        public List<Productionotemporary> GetYue(string And, string End)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Query<Productionotemporary>($"select ((SUM(Actualamount)/100)*10) BaiFen,a.Actualamount as YueZong,a.Zong BuBiao,b.DictionariesName Name from Thecontract a join DictionariesTable b on a.DictionariesId = b.DictionariesId where Contractsigningdate >= '{And}' and Contractsigningdate <= '{End}' group by b.DictionariesName, a.Zong, a.Actualamount").ToList();

                return list;
            }
        }

        /// <summary>
        /// 部门收费同比
        /// </summary>
        /// <param name="And"></param>
        /// <param name="End"></param>
        /// <returns></returns>
        public List<Productionotemporary> GetTongBi(string And,string End)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Query<Productionotemporary>($"select SUM(Actualamount) YueZong,((SUM(Actualamount)/100)*10) BaiFen,Zong BuBiao,b.DictionariesName Name from Thecontract a join DictionariesTable b on a.DictionariesId = b.DictionariesId where Contractsigningdate >= '2021-12-01' and Contractsigningdate <= '2021-12-31' group by Zong, b.DictionariesName").ToList();

                return list;
            }
        }

        /// <summary>
        /// 计算同比增减
        /// </summary>
        /// <param name="And"></param>
        /// <param name="End"></param>
        /// <returns></returns>
        public List<Productionotemporary> GetBiLi(string And, string End)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Query<Productionotemporary>($"select(select(select  sum(a.Actualamount) from Thecontract a where Contractsigningdate >= '2021-01-01' and Contractsigningdate <= '2021-12-31')-(select sum(b.Actualamount) from Thecontract b where Contractsigningdate >= '2021-12-01' and Contractsigningdate <= '2021-12-31'))as Suan,sum(a.Actualamount) Zonge,a.Actualamount YueZong, b.DictionariesName Name, a.Zong BuBiao,((SUM(Actualamount) / 100) * 10) SuanNian from Thecontract a join DictionariesTable b on a.DictionariesId = b.DictionariesId where Contractsigningdate>= '{And}' and Contractsigningdate<= '{End}'  and b.IsDelete='true' group by a.Actualamount,b.DictionariesName,a.Zong").ToList();

                return list;
            }
        }

        /// <summary>
        /// 合同收费详细
        /// </summary>
        /// <param name="DictionariesId"></param>
        /// <param name="ShiJian"></param>
        /// <param name="ContractName"></param>
        /// <returns></returns>
        public List<Thecontract> GetThecontractss(string DictionariesId,string ShiJian,string ContractName)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var sql = $"select * from Thecontract where 1=1 ";
                if (!string.IsNullOrEmpty(DictionariesId))
                {
                    sql += $" and DictionariesId='{Convert.ToInt32(DictionariesId)}' ";
                }
                if (!string.IsNullOrEmpty(ShiJian))
                {
                    sql += $" and Contractsigningdate like '%{ShiJian}%' ";
                }
                if (!string.IsNullOrEmpty(ContractName))
                {
                    sql += $" and ContractName like '%{ContractName}%'";
                }
                var list = conn.Query<Thecontract>(sql).ToList();

                return list;
            }
        }

        /// <summary>
        /// 部门收费同比
        /// </summary>
        /// <returns></returns>
        public List<Productionotemporary> GetDepartmentCharges(string And,string End)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Query<Productionotemporary>($"select b.DictionariesName Name,a.Zong BuBiao,SUM(a.Actualamount) YueZong,((SUM(Actualamount)/100)*10) BaiFen from Thecontract a join DictionariesTable b on a.DictionariesId = b.DictionariesId where Contractsigningdate >= '{And}' and Contractsigningdate <= '{End}' group by b.DictionariesName, a.Zong").ToList();

                return list;
            }
        }

        /// <summary>
        /// 环比
        /// </summary>
        /// <returns></returns>
        public List<Productionotemporary> GetHuanQian(string And, string End,string Ands,string Ends)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Query<Productionotemporary>($"select (select SUM(Actualamount) from Thecontract where month(Contractsigningdate) = {End} and year(Contractsigningdate) = {And}) as Qian, (select SUM(Actualamount) from Thecontract where month(Contractsigningdate) = {Ends} and year(Contractsigningdate) = {Ands}) as Hou").ToList();

                return list;
            }
        }
        /// <summary>
        /// 合同与项目综合统计
        /// </summary>
        /// <param name="deptid"></param>
        /// <param name="year"></param>
        /// <param name="ji"></param>
        /// <param name="yue"></param>
        /// <returns></returns>
        public ResultData ShowTotal(int deptid,int year,int ji,int yue)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var sql = $"select a.DeptId,a.DeptName,b.EnteringTime,count(b.ProjectId) as projectnum,count(c.ContractId) as contractnum,count(d.CustomerId) as customarnum from Department a left join Project b on a.DeptId = b.DeptId left join Thecontract c on b.contractId = c.ContractId left join Customer d on c.CustomerID = d.CustomerId group by a.DeptId,a.DeptName,b.EnteringTime having 1 = 1 ";
                if (deptid > 0)
                {
                    sql += $" and a.DeptId='{deptid}'";
                }
                if (year > 0)
                {
                    sql += $" and SUBSTRING(CONVERT(nvarchar(20),EnteringTime),7,4)='{year}'";
                }
                //if (ji > 0)
                //{
                //    sql += $" and b.DeptId='{deptid}'";
                //}
                if (yue > 0)
                {
                    sql += $" and SUBSTRING(CONVERT(nvarchar(20),EnteringTime),1,2)='{yue}'";
                }
                var list = conn.Query<ShowTotalDto>(sql);
                return new ResultData() { Code = 1, Data = list };
            }
        }
        /// <summary>
        /// 合同收款统计
        /// </summary>
        /// <param name="deptid"></param>
        /// <param name="year"></param>
        /// <param name="ji"></param>
        /// <param name="yue"></param>
        /// <param name="begintime"></param>
        /// <param name="endtime"></param>
        /// <param name="beinyear"></param>
        /// <param name="endyaer"></param>
        /// <param name="name"></param>
        /// <param name="beginedu"></param>
        /// <param name="endedu"></param>
        /// <param name="beginsalary"></param>
        /// <param name="endsalary"></param>
        /// <param name="beginjin"></param>
        /// <param name="endjin"></param>
        /// <returns></returns>
        public ResultData ShowContractTotal(ChecksDto checksDto)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var sql = "select a.ContractNo,a.ContractName,a.Originalamount,a.Actualamount,a.Contractallowedstatus,a.ProjectLeaderId,c.ProjectLeaderName,b.*,d.DeptId from Thecontract a join charges b on a.CId=b.CId join ProjectLeader c on a.ProjectLeaderId=c.ProjectLeaderId join Project d on a.ContractId=d.contractId where 1=1  ";
                if (checksDto.DeptId > 0)
                {
                    sql += $" and d.DeptId='{checksDto.DeptId}'";
                }
                if (checksDto.Year > 0)
                {
                    sql += $" and SUBSTRING(CONVERT(nvarchar(20),collectionTime),1,4)='{checksDto.Year}'";
                }
                if (checksDto.Ji > 0)
                {
                    //sql += $" and b.DeptId='{deptid}'";
                }
                if (checksDto.Yue > 0)
                {
                    sql += $" and SUBSTRING(CONVERT(nvarchar(20),collectionTime),6,2)='{checksDto.Yue}'";
                }
                //if(checksDto.BeginTime!=null)
                //{
                //    sql += $" and b.collectionTime>='{checksDto.BeginTime}'";
                //}
                //if (checksDto.EndTime != null)
                //{
                //    sql += $" and b.collectionTime<='{checksDto.EndTime}'";
                //}
                //if(checksDto.BeginYear> 0)
                //{
                //    sql += $" and SUBSTRING(CONVERT(nvarchar(20),collectionTime),1,4)>='{checksDto.BeginYear}'";
                //}
                //if(checksDto.EndYear> 0)
                //{
                //    sql += $" and SUBSTRING(CONVERT(nvarchar(20),collectionTime),1,4)<='{checksDto.EndYear}'";
                //}
                if(!string.IsNullOrWhiteSpace(checksDto.Name)&& checksDto.Name != "string")
                {
                    sql += $" and a.ContractName='{checksDto.Name}'";
                }
                //if (!string.IsNullOrWhiteSpace(checksDto.BeginEDu) && checksDto.BeginEDu != "string")
                //{
                //    sql += $" and a.Originalamount='{checksDto.BeginEDu}'";
                //}
                //if (!string.IsNullOrWhiteSpace(checksDto.EndEDu) && checksDto.EndEDu != "string")
                //{
                //    sql += $" and a.Originalamount='{checksDto.EndEDu}'";
                //}
                //if (checksDto.BeginSalary > 0)
                //{
                //    sql += $" and a.Actualamount='{checksDto.BeginSalary}'";
                //}
                //if (checksDto.EndSalary > 0)
                //{
                //    sql += $" and a.Actualamount='{checksDto.EndSalary}'";
                //}
                //if (!string.IsNullOrWhiteSpace(checksDto.BeginJin) && checksDto.BeginJin != "string")
                //{
                //    //sql += $" and a.Originalamount='{checksDto.BeginJin}'";
                //}
                //if (!string.IsNullOrWhiteSpace(checksDto.EndJin) && checksDto.EndJin != "string")
                //{
                //    //sql += $" and a.Originalamount='{checksDto.EndJin}'";
                //}
                var list = conn.Query<ShowContractTotalDto>(sql);
                return new ResultData() { Code = 1, Data = list };
            }
        }
        /// <summary>
        /// 合同综合统计
        /// </summary>
        /// <param name="checksDto"></param>
        /// <returns></returns>
        public ResultData ShowContractCount(ChecksDto checksDto)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var sql = $"select a.DeptName,SUBSTRING(CONVERT(nvarchar(20),c.Contractsigningdate),1,4) as Years,c.Originalamount as Num,c.Numberofdrawingsets as Nums,b.BeginTime,b.CompletionTime as BeginTime,b.CompletionTime as EndTime,b.CompletionTime from Department a join Project b on a.DeptId=b.DeptId join Thecontract c on b.contractId=c.ContractId group by a.DeptId,a.DeptName,c.Contractsigningdate,c.Originalamount,c.Numberofdrawingsets,b.BeginTime,b.CompletionTime having 1=1    ";
                if (checksDto.DeptId > 0)
                {
                    sql += $" and a.DeptId='{checksDto.DeptId}'";
                }
                if (checksDto.Year > 0)
                {
                    sql += $" and SUBSTRING(CONVERT(nvarchar(20),BeginTime),1,4)='{checksDto.Year}'";
                }
                //if (checksDto.Ji > 0)
                //{
                //    //sql += $" and b.DeptId='{deptid}'";
                //}
                if (checksDto.Yue > 0)
                {
                    sql += $" and SUBSTRING(CONVERT(nvarchar(20),BeginTime),6,2)='{checksDto.Yue}'";
                }
                var list = conn.Query<ShowContractCountDto>(sql);
                return new ResultData() { Code = 1, Data = list };
            }
        }

        /// <summary>
        /// 项目分类同比
        /// </summary>
        /// <returns></returns>
        public List<Productionotemporary> GetFenLei(string And, string End, string Ands, string Ends)
        {
            var sql = "ChaXun";

            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                conn.Open();

                var list = conn.Query<Productionotemporary>(sql,
                    new { And=And,End=End,Ands=Ands,Ends=Ends},
                    commandType: CommandType.StoredProcedure);

                return list.ToList();
            }
        }
        /// <summary>
        /// 项目综合统计
        /// </summary>
        /// <param name="checksDto"></param>
        /// <returns></returns>
        public ResultData ShowProjectCount(ChecksDto checksDto)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var sql = $"select a.DeptId,a.DeptName,count(b.ProjectId) as projectNum,b.Scale,b.EnteringTime from Department a join Project b on a.DeptId=b.DeptId group by a.DeptName,b.Scale,a.DeptId,b.EnteringTime having 1=1 ";
                if (checksDto.DeptId > 0)
                {
                    sql += $" and a.DeptId='{checksDto.DeptId}'";
                }
                if (checksDto.Year > 0)
                {
                    sql += $" and SUBSTRING(CONVERT(nvarchar(20),b.EnteringTime),7,4)='{checksDto.Year}'";
                }
                if (checksDto.Ji > 0)
                {
                    //sql += $" and b.DeptId='{deptid}'";
                }
                if (checksDto.Yue > 0)
                {
                    sql += $" and SUBSTRING(CONVERT(nvarchar(20),b.EnteringTime),1,2)='{checksDto.Yue}'";
                }
                var list = conn.Query<ShowProjectCountDto>(sql);
                return new ResultData() { Code = 1, Data = list };
            }
        }
    }
}
